Microsoft excels V lookup function is perhaps one of the most common one of the most popular more advanced
type functions bytes.
It has some limitations.
Vi look up let's talk about it for just a moment.
It's a vertical look up and what it essentially does is it takes a value such as an ID like what I have
on the screen right here the employee ID number one 054 takes that value and then goes into a lists
such as this master employee list here.
It looks through the first column of the list and it looks vertically three out list.
Now think about this for a moment.
What if you're a master list like the one that we're looking at here.
It's first column was not the employee ID.
Maybe it was the department or last name or first name or some other column was the first column in
the list.
The employee I.D. was maybe three four it is someplace in the middle of the list.
Well a vertical look up wouldn't be affected in that situation.
You'd have to redesign your list essentially by moving the employee ID to the first column within the
lists.
And that's perhaps not something that we can do or something that you want to do within your lists.
Something else about the we look at Once again it's a great great feature.
Very very popular.
Very very common but it's also very resource intensive.
If you've got a lot of Wii look ups happening within your work bike or or look ups your workbooks going
to tend to slow down when it calculates the data.
You've got hundreds of rows in there with hundreds of lookups you open up the workbook you might be
sitting there for a little while waiting for it to update its its calculation.
Well insteps two functions and I won't discuss here.
These two functions used together.
They're called index and match.
Are you going to remove these inadequacies that the Wii look up has.
You don't have to worry about the look of value being in the first column of your list.
You can look at either direction left or right for your returning value.
And there there tended to be more efficient for your system.
So let's take a look at how you use the index and match function and I want to talk about using them
independently first.
Just so your understanding of what index and match is and then we'll take a look at using them together
.
So take a look.
I'm here as an example violence again I'm on the Wii look up worksheets and here I've got my index and
match column and the index and match single column.
So first we're going to talking about using them independently and then Walt will talk about nesting
these functions together to simulate the idea of a Wii.
Look up to watch this here's first the end.
So an index function.
Essentially your Excel documents a big index.
It's a big grid.
You know if we look at this data over here like last names it's just a column of rows right column of
values here in row format.
And if we think of this as an index we've got row number one number two number three number four number
five number six and so on.
If I if I grab multiple columns here maybe I've got last name and first name and I want to find Mark
I want to know what position Mark here it is right here what position Mark is in this index.
This group of data.
Well it's in the second column and it's in one two three four five six.
Seventh row.
So second column seventh row with within this index.
This collection of data.
So watch this.
Here's the INDEX function.
I'll jump up here into G-3 and I'm going to create the index all up up to my formulas tab and I'm going
to look up and reference and let's find the one called index.
Now this is going to be a bit different than when you perhaps experience with other Microsoft Excel
functions.
There are two different flavors of working with this function.
I'm going to go ahead stick with the common one just the first one there are re.
You know what what's your collection of data what row number or column number are you looking for within
this array.
So I'll go ahead.
OK.
All right so once I know three things I'm all I'm really only to take advantage of two of them are R.A.
and Rowe are both bold signifying that these are required arguments that this function need call and
not bold.
So it's it's optional.
But if he did have multiple columns such as last name first name and a part and selected what which
column do you want to get out of this.
So my array.
Let's say as an example I just want to look for first names.
So my area I in a grad the first name column here and describing from to to see 18 and let's see I want
to know what's inside of the seventh row.
So I'll tap my 7 hit at my tab key here and I'll hit OK.
And there's Mark.
So it's just a big index.
It's a big collection of data.
They're calling it an array indexed array and position 1 of this particular array first names.
We got first name and the second index we got Howard in the third index we got Joe.
So on us on seven the next we got Mark.
So this is our index function and it returns data back to us based on a position.
All right so keep that in your mind.
Let's try and match them and hop over to the match column and once again back up the formulas back in
to look up and reference and let's find the match function.
All right now whereas the index function we gave it a numeric value such as 7 and it gave us returned
to us a value in the seventh position of that index of that array.
Now here's what the match does.
First thing wants to now what am I looking up.
What am I going to look for.
Well let's say let's look for one of the employee IDs this time.
So I'm just going to grab this 1 0 7 5 value.
All right.
Now now wants to know the second thing whoa where am I going to look for that.
Where am I going to find it at.
Well you're going to find it inside this column over here.
I'm going to grab this array this collection of data etude 18.
Now I am going to take advantage of the third option here.
If you're familiar with the Wii look up this might sound familiar to you as well.
It's a number 1 0 or negative 1 indicating which value to return.
Now you can actually put some other values in there as well.
What I'm doing here is I'm going to type in true true here and what this is going to do is it's going
to find the exact match very similar to that.
We look at that we spoke about earlier.
So I'm in put you in there will find the exact match I don't want to cause this thing to it and I'm
going to go ahead.
OK.
Now remember the index returned a value back to us based on a position.
This time it returns the position of the data back to us.
So hey I told it to define 1 0 7 5 right.
That's in the 1 2 3 4 5th position.
So now with match we can find it.
Piece of data bytes position with index we can find a value based on its position.
All right so that those are two separate ones.
Now why I knew more than likely you probably won't use these independently of each other more likely
than not.
The magic really comes into play when you nest them when you bring them together index and match.
Take a look.
Let's try this together.
It's out of my column.
I'm going to go to i3 and I'm going to use the index and match function to simulate the same kind of
concept that if the Look-Up does but at a much more robust a much more efficient method to perform in
a look up.
All right.
So my first step here is I'm going to actually use the index function.
So I'm going to go up to my formulas Navigon back to look up and reference and I'll find index.
All right.
Once again I'll pick the array of flavor.
OK.
All right remember must note three things here from us.
So first thing.
Well where am I going to be looking for data.
Well I guess first off what is it that we're looking for.
Well let's say you know what I want to look for last names.
Well I've got a list last things right here but I've also got this the last names on this master list
.
So it's actually hop over to master employee list and I'm going to grab the last name column here and
it's got a click up in the B1 and I'll do a control shift down arrow grab all of that.
There's the array.
All right now I'm looking for a specific last name.
Now we know that utilizing the index function I could give a numeric value say hey you know return what's
back inside the tenth call caller tenth row or the fifth row or the first row.
But you know you know what I don't want to just give it a numeric value say we'll just return one of
you finding that position.
I'm looking for a very specific last name.
Well the last name that I'm looking for is going to be based off of an ID.
All right.
So now in order for us to be able to determine where the IDs at so that gives me back the appropriate
last name.
I'm now going to nest a match function.
So instead of my row numb rather than just some number I'm throwing it out there.
I mean unless the match function to find the appropriate employee ID's position inside the master employee
list.
So now am I Rona am I going to bring in match.
Open up a print ceased now to make this a little bit cleaner.
I'm going to click up here Mike for me a block bar inside the match section and this will take me into
the match arguments.
All right so what am I looking for.
See I'm going to find employee number 1 0 6 sevens name.
Let's grab that.
Now while the lookup array.
Where is it going to find this that's wild for me.
I'm going to go back to master employee lists.
It's going to find the IDs inside the A column.
So I'll go to a one control shift down arrow grab all that.
And I'm going to tell it.
Let's do.
True here it's my the exact match.
All right.
So now I'm I'm finding where that employee ID is within that other lists tell the what to look for where
it's going to find it at.
All right.
Now this is just the match function.
Let's go back to index.
Let me a quick back up here in the index and let's see am I feeling good about this.
We're finding the array of all the names of the last names.
And we're going to use the row now the match function to build to find that position.
All right.
Well let's see.
I'm thinking I'm thinking here we've got the we've got the call we've got the master employee list there
.
I think I'm feeling good about this I'm going to go ahead.
OK scoots.
Is that right.
1 0 6 7.
Scott how do you pronounce that.
Inside the last name that looks right to me I mean a hopper to my master employee list listless look
at this.
Let's see.
1 0 6 7 there it is.
It's inside of the fourth row.
And it was able to find Scott based off of this index match combination very similar to the vertical
look up once again but the column that it's looking through doesn't have to be the first column and
pulley idea could be any of the columns here.
But based on position with in this set of data we can easily and effectively find this data.
So try this out try it out a few times really get a grasp for this.
Look for a few different last names or try different things.
Look for last names look for pay rate.
Look for the department whatever it might be based off of employee I.D. or whatever you want to look
for.
Try this out